﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.IO;
using CTEM.Controllers.Global;

using CTEM.Models;
using System.Diagnostics;
using CTEM.Controllers.Global;

namespace CTEM.Controllers.DAO
{
    class CS_EstimateExpensesDAO
    {
        /// <summary>
        /// 
        /// </summary>
        public CS_EstimateExpensesDAO()
        {
            Transaction = null;
        }

        /// <summary>
        /// 
        /// </summary>
        private StackFrame _currentStackFrame;

        /// <summary>
        /// 
        /// </summary>
        public SqlTransaction Transaction { get; set; }

        /// <summary>
        /// 
        /// </summary>
        private SqlConnection _con;

        /// <summary>
        /// 
        /// </summary>
        SqlConnection Connection
        {
            get
            {
                if (_con == null)
                    _con = new SqlConnection(DatabaseInfo.ConnectionString);
                if (_con.State == ConnectionState.Closed)
                {
                    _con.Open();
                }
                return _con;
            }
            set
            {
                _con = value;
            }
        }

        #region Data Access Controler for CS_EstimateExpenses Table
        /// <summary>
        /// 
        /// </summary>
        /// <param name="estexp"></param>
        /// <returns></returns>
        public long CreateEstimateExpenses(CS_EstimateExpensesDTO estexp)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_CreateEstimateExpenses]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames =  { "@ConstructionID", 
                                                   "@ConstructionType", 
                                                   "@MaterialEstimate", 
                                                   "@MaterialExpenses", 
                                                   "@WorkerEstimate", 
                                                   "@WorkerExpenses", 
                                                   "@MachineEstimate", 
                                                   "@MachineExpenses", 
                                                   "@TransportEstimate", 
                                                   "@TransportExpenses", 
                                                   "@OthersEstimate", 
                                                   "@OthersExpenses", 
                                                   "@CreatedBy", 
                                                   "@CreatedDate" };
                    object[] parametervalues =  { estexp.ConstructionID, 
                                                    estexp.ConstructionType, 
                                                    estexp.MaterialEstimate, 
                                                    estexp.MaterialExpenses, 
                                                    estexp.WorkerEstimate, 
                                                    estexp.WorkerExpenses, 
                                                    estexp.MachineEstimate, 
                                                    estexp.MachineExpenses, 
                                                    estexp.TransportEstimate, 
                                                    estexp.TransportExpenses, 
                                                    estexp.OthersEstimate, 
                                                    estexp.OthersExpenses, 
                                                    estexp.CreatedBy, 
                                                    estexp.CreatedDate };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            return Convert.ToInt64(reader[0]);
                    }
                    return 0;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estexp"></param>
        /// <returns></returns>
        internal bool UpdateEstimateExpenses(CS_EstimateExpensesDTO estexp)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_UpdateEstimateExpenses]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames =  { "@ConstructionID", 
                                                   "@MaterialEstimate", 
                                                   "@MaterialExpenses", 
                                                   "@WorkerEstimate", 
                                                   "@WorkerExpenses", 
                                                   "@MachineEstimate", 
                                                   "@MachineExpenses", 
                                                   "@TransportEstimate", 
                                                   "@TransportExpenses", 
                                                   "@OthersEstimate", 
                                                   "@OthersExpenses", 
                                                   "@UpdatedBy", 
                                                   "@LastUpdated" };
                    object[] parametervalues =  { estexp.ConstructionID, 
                                                    estexp.MaterialEstimate, 
                                                    estexp.MaterialExpenses, 
                                                    estexp.WorkerEstimate, 
                                                    estexp.WorkerExpenses, 
                                                    estexp.MachineEstimate, 
                                                    estexp.MachineExpenses, 
                                                    estexp.TransportEstimate, 
                                                    estexp.TransportExpenses, 
                                                    estexp.OthersEstimate, 
                                                    estexp.OthersExpenses, 
                                                    estexp.UpdatedBy, 
                                                    estexp.LastUpdated };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    cmd.ExecuteReader();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estexpID"></param>
        /// <returns></returns>
        public bool DeleteEstimateExpenses(long estexpID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_DeleteEstimateExpenses]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    cmd.Parameters.Add(new SqlParameter("@EstimateExpensesID", estexpID));
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="isactive"></param>
        /// <returns></returns>
        public List<CS_EstimateExpensesDTO> LoadAllEstimateExpenses(bool isactive)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetAllEstimateExpenses]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                if (isactive)
                {
                    cmd.Parameters.Add(new SqlParameter("@IsActive", isactive));
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@IsActive", DBNull.Value));
                }
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<CS_EstimateExpensesDTO> listestexps = new List<CS_EstimateExpensesDTO>();
                        while (reader.Read())
                        {
                            CS_EstimateExpensesDTO estexpDTO = new CS_EstimateExpensesDTO
                            {
                                EstimateExpensesID = Convert.ToInt64(reader["EstimateExpensesID"]),
                                ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                                ConstructionType = Convert.ToString(reader["ConstructionType"]),
                                MaterialEstimate = Convert.ToInt64(reader["MaterialEstimate"]),
                                MaterialExpenses = Convert.ToInt64(reader["MaterialExpenses"]),
                                MachineEstimate = Convert.ToInt64(reader["MachineEstimate"]),
                                MachineExpenses = Convert.ToInt64(reader["MachineExpenses"]),
                                WorkerEstimate = Convert.ToInt64(reader["WorkerEstimate"]),
                                WorkerExpenses = Convert.ToInt64(reader["WorkerExpenses"]),
                                TransportEstimate = Convert.ToInt64(reader["TransportEstimate"]),
                                TransportExpenses = Convert.ToInt64(reader["TransportExpenses"]),
                                OthersEstimate = Convert.ToInt64(reader["OthersEstimate"]),
                                OthersExpenses = Convert.ToInt64(reader["OthersExpenses"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            estexpDTO.TotalEstimates = estexpDTO.MaterialEstimate + estexpDTO.MachineEstimate + estexpDTO.WorkerEstimate + estexpDTO.TransportEstimate + estexpDTO.OthersEstimate;
                            estexpDTO.TotalExpenses = estexpDTO.MaterialExpenses + estexpDTO.MachineExpenses + estexpDTO.WorkerExpenses + estexpDTO.TransportExpenses + estexpDTO.OthersExpenses;
                            listestexps.Add(estexpDTO);
                        }
                        return listestexps;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="consID"></param>
        /// <returns></returns>
        public CS_EstimateExpensesDTO LoadEstimateExpensesByConstructionID(long consID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetEstimateIDByConstructionID]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ConstructionID", consID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            CS_EstimateExpensesDTO estexpDTO = new CS_EstimateExpensesDTO
                            {
                                EstimateExpensesID = Convert.ToInt64(reader["EstimateExpensesID"]),
                                ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                                ConstructionType = Convert.ToString(reader["ConstructionType"]),
                                MaterialEstimate = Convert.ToInt64(reader["MaterialEstimate"]),
                                MaterialExpenses = Convert.ToInt64(reader["MaterialExpenses"]),
                                MachineEstimate = Convert.ToInt64(reader["MachineEstimate"]),
                                MachineExpenses = Convert.ToInt64(reader["MachineExpenses"]),
                                WorkerEstimate = Convert.ToInt64(reader["WorkerEstimate"]),
                                WorkerExpenses = Convert.ToInt64(reader["WorkerExpenses"]),
                                TransportEstimate = Convert.ToInt64(reader["TransportEstimate"]),
                                TransportExpenses = Convert.ToInt64(reader["TransportExpenses"]),
                                OthersEstimate = Convert.ToInt64(reader["OthersEstimate"]),
                                OthersExpenses = Convert.ToInt64(reader["OthersExpenses"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            estexpDTO.TotalEstimates = estexpDTO.MaterialEstimate + estexpDTO.MachineEstimate + estexpDTO.WorkerEstimate + estexpDTO.TransportEstimate + estexpDTO.OthersEstimate;
                            estexpDTO.TotalExpenses = estexpDTO.MaterialExpenses + estexpDTO.MachineExpenses + estexpDTO.WorkerExpenses + estexpDTO.TransportExpenses + estexpDTO.OthersExpenses;
                            return estexpDTO;
                        }
                        return null;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }
        #endregion

        #region Data Access Controler for CS_EstimateDetail Table
        /// <summary>
        /// 
        /// </summary>
        /// <param name="estdt"></param>
        /// <returns></returns>
        public long CreateEstimateDetail(CS_EstimateDetailDTO estdt)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_CreateEstimateDetail]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@EstimateExpensesID", "@GroupMaterialID", "@Quantity", "@Price", "@TotalCost", "@Note" };
                    object[] parametervalues = { estdt.EstimateExpensesID, estdt.GroupMaterialID, estdt.Quantity, estdt.Price, estdt.TotalCost, estdt.Note };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            return Convert.ToInt64(reader[0]);
                    }
                    return 0;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estexp"></param>
        /// <returns></returns>
        internal bool UpdateEstimateDetail(CS_EstimateDetailDTO estdt)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_UpdateEstimateDetail]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@EstimateDetailID", "@EstimateExpensesID", "@GroupMaterialID", "@Quantity", "@Price", "@TotalCost", "@Note" };
                    object[] parametervalues = { estdt.EstimateDetailID, estdt.EstimateExpensesID, estdt.GroupMaterialID, estdt.Quantity, estdt.Price, estdt.TotalCost, estdt.Note };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    cmd.ExecuteReader();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estdtID"></param>
        /// <returns></returns>
        public bool DeleteEstimateDetail(long estdtID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_DeleteEstimateDetail]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    cmd.Parameters.Add(new SqlParameter("@EstimateDetailID", estdtID));
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estexpeID"></param>
        /// <returns></returns>
        public List<CS_EstimateDetailDTO> LoadEstimateDetail(long estexpeID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetEstimateDetail]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@EstimateExpensesID", estexpeID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<CS_EstimateDetailDTO> listestdts = new List<CS_EstimateDetailDTO>();
                        while (reader.Read())
                        {
                            CS_EstimateDetailDTO estdtDTO = new CS_EstimateDetailDTO
                            {
                                EstimateDetailID = Convert.ToInt64(reader["EstimateDetailID"]),
                                EstimateExpensesID = Convert.ToInt64(reader["EstimateExpensesID"]),
                                GroupMaterialID = Convert.ToInt64(reader["GroupMaterialID"]),
                                Quantity = Convert.ToDouble(reader["Quantity"]),
                                Price = Convert.ToDouble(reader["Price"]),
                                TotalCost = Convert.ToDouble(reader["TotalCost"]),
                                Note = Convert.ToString(reader["Note"])
                            };
                            listestdts.Add(estdtDTO);
                        }
                        return listestdts;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estdtID"></param>
        /// <returns></returns>
        public CS_EstimateDetailDTO LoadEstimateDetailByID(long estdtID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetEstimateDetailByID]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@EstimateDetailID", estdtID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            CS_EstimateDetailDTO estdtDTO = new CS_EstimateDetailDTO
                            {
                                EstimateDetailID = Convert.ToInt64(reader["EstimateDetailID"]),
                                EstimateExpensesID = Convert.ToInt64(reader["EstimateExpensesID"]),
                                GroupMaterialID = Convert.ToInt64(reader["GroupMaterialID"]),
                                Quantity = Convert.ToDouble(reader["Quantity"]),
                                Price = Convert.ToDouble(reader["Price"]),
                                TotalCost = Convert.ToDouble(reader["TotalCost"]),
                                Note = Convert.ToString(reader["Note"])
                            };
                            return estdtDTO;
                        }
                        return null;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }
        #endregion

        #region Data Access Controler for CS_EstimateIriDetail Table
        /// <summary>
        /// 
        /// </summary>
        /// <param name="estiridt"></param>
        /// <returns></returns>
        public long CreateEstimateIriDetail(CS_EstimateIriDetailDTO estiridt)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_CreateEstimateIriDetail]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@EstimateExpensesID", "@Weight", "@Length", "@Containers", "@TotalCost", "@Note" };
                    object[] parametervalues = { estiridt.EstimateExpensesID, estiridt.Weight, estiridt.Length, estiridt.Containers, estiridt.TotalCost, estiridt.Note };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            return Convert.ToInt64(reader[0]);
                    }
                    return 0;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estexp"></param>
        /// <returns></returns>
        internal bool UpdateEstimateIriDetail(CS_EstimateIriDetailDTO estiridt)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_UpdateEstimateIriDetail]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@EstimateIriDetailID", "@EstimateExpensesID", "@Weight", "@Length", "@Containers", "@TotalCost", "@Note" };
                    object[] parametervalues = { estiridt.EstimateIriDetailID, estiridt.EstimateExpensesID, estiridt.Weight, estiridt.Length, estiridt.Containers, estiridt.TotalCost, estiridt.Note };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    cmd.ExecuteReader();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estiridtID"></param>
        /// <returns></returns>
        public bool DeleteEstimateIriDetail(long estiridtID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_DeleteEstimateIriDetail]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    cmd.Parameters.Add(new SqlParameter("@EstimateIriDetailID", estiridtID));
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="estexpeID"></param>
        /// <returns></returns>
        public List<CS_EstimateIriDetailDTO> LoadEstimateIriDetail(long estexpeID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetEstimateIriDetail]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@EstimateExpensesID", estexpeID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<CS_EstimateIriDetailDTO> listestiridts = new List<CS_EstimateIriDetailDTO>();
                        while (reader.Read())
                        {
                            CS_EstimateIriDetailDTO estiridtDTO = new CS_EstimateIriDetailDTO
                            {
                                EstimateIriDetailID = Convert.ToInt64(reader["EstimateIriDetailID"]),
                                EstimateExpensesID = Convert.ToInt64(reader["EstimateExpensesID"]),
                                Weight = Convert.ToInt64(reader["Weight"]),
                                Length = Convert.ToDouble(reader["Length"]),
                                Containers = Convert.ToDouble(reader["Containers"]),
                                TotalCost = Convert.ToDouble(reader["TotalCost"]),
                                Note = Convert.ToString(reader["Note"])
                            };
                            listestiridts.Add(estiridtDTO);
                        }
                        return listestiridts;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }
        #endregion
    }
}
